﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using System.Configuration;
using System.Data.SqlClient;

namespace SQLDao
{
    public class SQL_Role_dao : IRoleDAO
    {
        private SqlConnection connection;
        public SQL_Role_dao()
        {
            var sql_dao = new SQL_Dao();
            this.connection = SQL_Dao.Connection;
        }

        public Dictionary<int, string> GetRolesList()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [id], [title] FROM [Roles]");

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    var lst = new Dictionary<int, string>();
                    while (reader.Read())
                    {
                        lst.Add((int)reader["id"], (string)reader["title"]);
                    }
                    return lst;
                }
                return null;
            }
        }

        public void CreateRole(string title)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("INSERT INTO [Roles] ([title]) VALUES ('{0}')", title);
            command.ExecuteNonQuery();
        }

        public void DeleteRole(int roleID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("DELETE FROM [Roles] WHERE [id]='{0}'", roleID);
            command.ExecuteNonQuery();
        }

        public bool RoleExists(int roleID)
        {
            if (GetRole(roleID) == null)
            {
                return false;
            }
            return true;
        }

        public bool RoleExists(string title)
        {
            if (GetRoleID(title) == -1)
            {
                return false;
            }
            return true;
        }

        public int GetRoleID(string title)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [id] FROM [Roles] WHERE [title]='{0}'", title);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return (int)reader["id"];
                }
                return -1;
            }
        }

        public string GetRole(int roleID)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = this.connection;
            command.CommandText = string.Format("SELECT [title] FROM [Roles] WHERE [id]='{0}'", roleID);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return (string)reader["title"];
                }
                return null;
            }
        }
    }
}
